Creación base de datos:


CREATE DATABASE ecodosruedas;


Bicicleta:

CREATE TABLE `bicicleta` (
 `id_bicicleta` int(11) NOT NULL AUTO_INCREMENT,
 `nombre_fabricante` varchar(100) NOT NULL,
 `precio_unitario` decimal(10,2) NOT NULL,
 `anio_construccion` year(4) DEFAULT NULL,
 PRIMARY KEY (`id_bicicleta`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


Cliente:



cliente	CREATE TABLE `cliente` (
 `alias` varchar(50) NOT NULL,
 `nombres` varchar(100) NOT NULL,
 `apellidos` varchar(100) NOT NULL,
 `email` varchar(100) NOT NULL,
 `celular` varchar(20) DEFAULT NULL,
 `contraseña` int(11) DEFAULT NULL CHECK (octet_length(`contraseña`) = 8),
 `fecha_nacimiento` date DEFAULT NULL,
 PRIMARY KEY (`alias`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Intención compra:

intencioncompra	CREATE TABLE `intencioncompra` (
 `id_intencion` int(11) NOT NULL AUTO_INCREMENT,
 `alias_cliente` varchar(50) DEFAULT NULL,
 `id_bicicleta` int(11) DEFAULT NULL,
 `id_moto` int(11) DEFAULT NULL,
 `fecha_hora` datetime NOT NULL,
 PRIMARY KEY (`id_intencion`),
 KEY `alias_cliente` (`alias_cliente`),
 KEY `id_bicicleta` (`id_bicicleta`),
 KEY `id_moto` (`id_moto`),
 CONSTRAINT `intencioncompra_ibfk_1` FOREIGN KEY (`alias_cliente`) REFERENCES `cliente` (`alias`),
 CONSTRAINT `intencioncompra_ibfk_2` FOREIGN KEY (`id_bicicleta`) REFERENCES `bicicleta` (`id_bicicleta`),
 CONSTRAINT `intencioncompra_ibfk_3` FOREIGN KEY (`id_moto`) REFERENCES `motocicletaelectrica` (`id_moto`)
) ENGINE=InnoDB AUTO_INCREMENT=69 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci

Motos eléctricas:

motocicletaelectrica	CREATE TABLE `motocicletaelectrica` (
 `id_moto` int(11) NOT NULL AUTO_INCREMENT,
 `nombre_fabricante` varchar(100) NOT NULL,
 `precio_unitario` decimal(10,2) NOT NULL,
 `autonomia_bateria` int(11) NOT NULL,
 `id_proveedor` int(11) DEFAULT NULL,
 PRIMARY KEY (`id_moto`),
 KEY `id_proveedor` (`id_proveedor`),
 CONSTRAINT `motocicletaelectrica_ibfk_1` FOREIGN KEY (`id_proveedor`) REFERENCES `proveedor` (`id_proveedor`)
) ENGINE=InnoDB AUTO_INCREMENT=8 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


proovedor: 

proveedor	CREATE TABLE `proveedor` (
 `id_proveedor` int(11) NOT NULL AUTO_INCREMENT,
 `nombre` varchar(100) NOT NULL,
 `direccion` varchar(150) DEFAULT NULL,
 `telefono` varchar(20) DEFAULT NULL,
 PRIMARY KEY (`id_proveedor`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_general_ci


tablas:


INSERT INTO `bicicleta` (`id_bicicleta`, `nombre_fabricante`, `precio_unitario`, `anio_construccion`) VALUES
(1, 'Cannondale', 1200000.00, '2020'),
(2, 'Trek', 1450000.00, '2019'),
(3, 'Yeti', 2000000.00, '2020'),
(4, 'Fuji', 950000.00, '2021'),
(5, 'Bmc', 1950000.00, '2018');

--
-- Volcado de datos para la tabla `cliente`
--

INSERT INTO `cliente` (`alias`, `nombres`, `apellidos`, `email`, `celular`, `contraseña`, `fecha_nacimiento`) VALUES
('diva', 'Ana', 'Diaz', 'ana.diaz@example.com', '3123456789', 34567890, '1995-09-10'),
('dreamer', 'Luis', 'Rojas', 'luis.rojas@example.com', '3134567890', 45678901, '1988-12-05'),
('green', 'Jorge', 'Rodriguez', 'jorge.rodriguez@example.com', '3178901234', 89012345, '1989-11-19'),
('lucky', 'Pedro', 'Perez', 'pedro.perez@example.com', '3101234567', 12345678, '1990-03-15'),
('malopez', 'Maria', 'Lopez', 'maria.lopez@example.com', '3112345678', 23456789, '1992-06-21'),
('neon', 'Nelson', 'Ruiz', 'nelson.ruiz@example.com', '3156789012', 67890123, '1991-07-07'),
('ninja', 'Andres', 'Cruz', 'andres.cruz@example.com', '3145678901', 56789012, '1993-02-28'),
('rose', 'Claudia', 'Mendez', 'claudia.mendez@example.com', '3167890123', 78901234, '1996-01-30');

--
-- Volcado de datos para la tabla `intencioncompra`
--

INSERT INTO `intencioncompra` (`id_intencion`, `alias_cliente`, `id_bicicleta`, `id_moto`, `fecha_hora`) VALUES
(52, 'lucky', 1, NULL, '2017-10-25 20:00:00'),
(53, 'lucky', 2, NULL, '2019-03-15 18:30:00'),
(54, 'lucky', NULL, 2, '2019-05-20 20:30:00'),
(55, 'malopez', 1, NULL, '2018-05-20 20:30:00'),
(56, 'malopez', NULL, 2, '2020-01-20 20:30:00'),
(57, 'diva', 3, NULL, '2019-05-20 20:30:00'),
(58, 'diva', 4, NULL, '2018-06-22 21:30:00'),
(59, 'diva', NULL, 3, '2020-03-17 15:30:20'),
(60, 'dreamer', NULL, 3, '2020-03-17 15:30:20'),
(61, 'dreamer', NULL, 4, '2020-04-10 18:30:20'),
(62, 'ninja', NULL, 5, '2020-02-17 20:30:20'),
(63, 'ninja', NULL, 2, '2020-02-20 16:30:20'),
(64, 'ninja', NULL, 6, '2020-03-27 18:30:20'),
(65, 'rose', NULL, 7, '2020-03-20 21:30:20'),
(66, 'green', 3, NULL, '2020-01-10 17:30:20'),
(67, 'green', 2, NULL, '2020-02-15 20:30:20'),
(68, 'green', 5, NULL, '2020-03-17 18:30:20');

--
-- Volcado de datos para la tabla `motocicletaelectrica`
--

INSERT INTO `motocicletaelectrica` (`id_moto`, `nombre_fabricante`, `precio_unitario`, `autonomia_bateria`, `id_proveedor`) VALUES
(2, 'Starker', 4200000.00, 18, 1),
(3, 'Lucky Lion', 5600000.00, 14, 2),
(4, 'Be Electric', 4600000.00, 26, 1),
(5, 'Aima', 8000000.00, 36, 3),
(6, 'Mec de Colombia', 5900000.00, 20, 4),
(7, 'Atom Electric', 4500000.00, 12, 5);

--
-- Volcado de datos para la tabla `proveedor`
--

INSERT INTO `proveedor` (`id_proveedor`, `nombre`, `direccion`, `telefono`) VALUES
(1, 'Auteco', 'calle7no4517', '05713224459'),
(2, 'Hitachi', 'calle19no10826', '05714223344'),
(3, 'Bosch', 'carrera68no2645', '05715678798'),
(4, 'Teco', 'calle77no6833', '05712213243'),
(5, 'General Electric', 'calle29no2612', '05712213243');
COMMIT;


Cambios:

UPDATE `bicicleta` SET `anio_construccion` = '2017' WHERE `bicicleta`.`id_bicicleta` = 1;

UPDATE `cliente` SET `celular`='3115678432' WHERE `cliente`.`nombres`= 'Andres';

DELETE FROM intencioncompra WHERE `intencioncompra`.`id_intencion` = 67;


Consultas:

SELECT `nombre_fabricante`, `precio_unitario`, `anio_construccion` FROM `bicicleta` WHERE `anio_construccion` > 2019;


SELECT nombre_fabricante FROM bicicleta ORDER BY nombre_fabricante ASC;
SELECT nombre_fabricante FROM motocicletaelectrica ORDER BY nombre_fabricante ASC;


SELECT me.nombre_fabricante FROM motocicletaelectrica AS me JOIN proveedor AS p ON me.id_proveedor = p.id_proveedor WHERE p.nombre = 'Auteco';


SELECT b.nombre_fabricante AS Fabricante FROM intencioncompra AS ic JOIN bicicleta AS b ON ic.id_bicicleta = b.id_bicicleta WHERE ic.alias_cliente = 'lucky' UNION SELECT me.nombre_fabricante AS Fabricante FROM intencioncompra AS ic JOIN motocicletaelectrica AS me ON ic.id_moto = me.id_moto WHERE ic.alias_cliente = 'lucky' ORDER BY Fabricante;



SELECT c.alias, c.nombres, c.apellidos FROM cliente AS c JOIN intencioncompra AS ic ON c.alias = ic.alias_cliente JOIN bicicleta AS b ON ic.id_bicicleta = b.id_bicicleta WHERE b.nombre_fabricante = 'Yeti' ORDER BY c.nombres, c.apellidos;







Consultas INNER JOIN :

SELECT
  c.alias,
  b.nombre_fabricante
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta;


SELECT
  me.nombre_fabricante AS NombreFabricanteMoto,
  p.nombre AS NombreProveedor
FROM motocicletaelectrica AS me
JOIN proveedor AS p
  ON me.id_proveedor = p.id_proveedor;


SELECT DISTINCT
  c.nombres,
  c.apellidos,
  c.celular
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
WHERE
  ic.id_moto IS NOT NULL;


SELECT
  c.nombres AS NombreCliente,
  c.apellidos AS ApellidoCliente,
  b.nombre_fabricante AS FabricanteBicicleta,
  b.anio_construccion AS AnioBicicleta,
  ic.fecha_hora AS FechaConsulta
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta
WHERE
  YEAR(ic.fecha_hora) > 2018;



SELECT
  c.alias,
  CONCAT(c.nombres, ' ', c.apellidos) AS NombreCompletoCliente,
  me.nombre_fabricante AS FabricanteMotocicleta
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
JOIN motocicletaelectrica AS me
  ON ic.id_moto = me.id_moto
WHERE
  me.id_proveedor IS NOT NULL;



SELECT
  c.alias,
  me.nombre_fabricante AS FabricanteMotocicleta,
  me.autonomia_bateria AS AutonomiaHoras
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
JOIN motocicletaelectrica AS me
  ON ic.id_moto = me.id_moto
WHERE
  me.autonomia_bateria > 20;



SELECT DISTINCT
  c.alias,
  c.nombres,
  c.apellidos
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
LEFT JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta
LEFT JOIN motocicletaelectrica AS me
  ON ic.id_moto = me.id_moto
WHERE
  b.nombre_fabricante = 'Yeti' OR me.nombre_fabricante = 'Starker';


SELECT
  c.alias,
  COALESCE(b.nombre_fabricante, me.nombre_fabricante) AS ProductoConsultado,
  ic.fecha_hora AS FechaConsulta
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
LEFT JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta
LEFT JOIN motocicletaelectrica AS me
  ON ic.id_moto = me.id_moto
WHERE
  YEAR(ic.fecha_hora) BETWEEN 2019 AND 2020;



SELECT
  b.nombre_fabricante AS FabricanteBicicleta,
  b.anio_construccion AS AnioConstruccion
FROM intencioncompra AS ic
JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta
WHERE
  ic.alias_cliente = 'green';


SELECT
  c.alias,
  COALESCE(b.nombre_fabricante, me.nombre_fabricante) AS Producto,
  CASE
    WHEN ic.id_bicicleta IS NOT NULL
    THEN 'Bicicleta'
    WHEN ic.id_moto IS NOT NULL
    THEN 'Motocicleta Eléctrica'
    ELSE 'Desconocido'
  END AS TipoProducto
FROM cliente AS c
JOIN intencioncompra AS ic
  ON c.alias = ic.alias_cliente
LEFT JOIN bicicleta AS b
  ON ic.id_bicicleta = b.id_bicicleta
LEFT JOIN motocicletaelectrica AS me
  ON ic.id_moto = me.id_moto
ORDER BY
  c.alias,
  TipoProducto;
